﻿using crawler.Models;
using Npgsql;
using NpgsqlTypes;
using System;
using System.Collections.Generic;
using crawler.Enums;

namespace crawler
{
    /// <summary>
    /// Class responsible for database logic.
    /// </summary>
    class Database
    {
        private Logger logger = Logger.getLogger();

        private string server = "localhost";
        private string port = "5432";
        private string userId = "postgres";
        private string password = "moviedb";
        private string database = "movies";
        private string connectionString = "Server={0};Port={1};User Id={2};Password={3};Database={4};";

        private NpgsqlConnection connection;

        Dictionary<string, int> IDgenreMapping = new Dictionary<string, int>()
        {
            {"Action",      1},
            {"Adventure",   2},
            {"Animation",   3},
            {"Biography",   4},
            {"Comedy",      5},
            {"Crime",       6},
            {"Documentary", 7},
            {"Drama",       8},
            {"Family",      9},
            {"Fantasy",     10},
            {"Film-Noir",   11},
            {"History",     12},
            {"Horror",      13},
            {"Music",       14},
            {"Musical",     15},
            {"Mystery",     16},
            {"Romance",     17},
            {"Sci-Fi",      18},
            {"Short",       19},
            {"Sport",       20},
            {"Thriller",    21},
            {"War",         22},
            {"Western",     23}
        };


        /// <summary>
        /// Opens connection to database.
        /// </summary>
        public void openConnection()
        {
            logger.log("Opening connection to database...");

            connection = new NpgsqlConnection(String.Format(connectionString, server, port, userId, password, database));
            connection.Open();

            if (connection == null)
            {
                throw new Exception("Failed to open database connection.");
            }

            logger.log("Connection to database established.");
        }

        /// <summary>
        /// Closes connection to database.
        /// </summary>
        public void closeConnection()
        {
            logger.log("Closing connection to database...");

            connection.Close();

            logger.log("Connection to database closed.");
        }

        /// <summary>
        /// Checks if movie is already present in database.
        /// </summary>
        /// <param name="film">Only name, year, duration and imdbID properties are set.</param>
        /// <returns></returns>
        public bool isMoviePresentInDatabase(Film film)
        {
            NpgsqlCommand command = new NpgsqlCommand("SELECT COUNT(*) FROM movie WHERE imdbId=:param", connection);

            command.Parameters.Add("param", NpgsqlDbType.Integer);
            command.Parameters[0].Value = film.ImdbId;

            long records = (long)command.ExecuteScalar();
            return records != 0;
        }

        /// <summary>
        /// Method used to store movie and related information to database.
        /// </summary>
        /// <param name="film"></param>
        /// <param name="directors"></param>
        /// <param name="writers"></param>
        /// <param name="genres"></param>
        /// <param name="reviews"></param>
        public void persistMovieToDatabase(Film film, List<Director> directors, List<Writer> writers, List<Genre> genres, List<Review> reviews)
        {
            NpgsqlCommand insertMovieCmd = new NpgsqlCommand(@"INSERT INTO movie(name, imdbid, year, duration, imdbscore, imdbmalescore,
            imdbfemalescore, imdbbelow18score, imdb18_29score, imdb30_44score, imdbabove45score, imdbnumofvoters, metascore, 
            metascorenumofcritics, mpaarating) VALUES(:name, :imdbid, :year, :duration, :imdbscore, :imdbmalescore, :imdbfemalescore, 
            :imdbbelow18score, :imdb18_29score, :imdb30_44score, :imdbabove45score, :imdbnumofvoters, :metascore, :metascorenumofcritics, 
            :mpaarating) RETURNING idMovie", connection);

            NpgsqlParameter[] movieParamList = { 
                                              new NpgsqlParameter("name", NpgsqlDbType.Text)                     { Value = film.Name },
                                              new NpgsqlParameter("imdbid", NpgsqlDbType.Integer)                { Value = film.ImdbId },
                                              new NpgsqlParameter("year", NpgsqlDbType.Integer)                  { Value = film.Year },
                                              new NpgsqlParameter("duration", NpgsqlDbType.Integer)              { Value = film.Duration },
                                              new NpgsqlParameter("imdbscore", NpgsqlDbType.Double)              { Value = film.ImdbScore },
                                              new NpgsqlParameter("imdbmalescore", NpgsqlDbType.Double)          { Value = film.ImdbMaleScore },
                                              new NpgsqlParameter("imdbfemalescore", NpgsqlDbType.Double)        { Value = film.ImdbFemaleScore },
                                              new NpgsqlParameter("imdbbelow18score", NpgsqlDbType.Double)       { Value = film.ImdbBelow18Score },
                                              new NpgsqlParameter("imdb18_29score", NpgsqlDbType.Double)         { Value = film.Imdb18_29Score },
                                              new NpgsqlParameter("imdb30_44score", NpgsqlDbType.Double)         { Value = film.Imdb30_44Score },
                                              new NpgsqlParameter("imdbabove45score", NpgsqlDbType.Double)       { Value = film.ImdbAbove45Score },
                                              new NpgsqlParameter("imdbnumofvoters", NpgsqlDbType.Integer)       { Value = film.NumOfImdbScoreVoters },
                                              new NpgsqlParameter("metascore", NpgsqlDbType.Integer)             { Value = film.MetaScore },
                                              new NpgsqlParameter("metascorenumofcritics", NpgsqlDbType.Integer) { Value = film.NumOfMetaScoreVoters },
                                              new NpgsqlParameter("mpaarating", NpgsqlDbType.Text)               { Value = film.MPAARating }
                                           };

            insertMovieCmd.Parameters.AddRange(movieParamList);

            int idMovie = -1;
            object insertResult = insertMovieCmd.ExecuteScalar();

            if (insertResult == null)
            {
                throw new Exception("Insert into movie table failed");
            }
            else
            {
                idMovie = Convert.ToInt32(insertResult);
            }

            foreach (Director director in directors)
            {
                int directorID = getDirectorID(director.Name);
                if (directorID == -1)
                {
                    directorID = persistDirectorToDB(director.Name);
                }

                if (directorID != -1)
                {
                    NpgsqlCommand insertMovieDirectorCmd = new NpgsqlCommand(@"INSERT INTO movie_director(idmovie, iddirector) VALUES (:idmovie, :iddirector)", connection);

                    NpgsqlParameter[] directorParamList = {
                                                           new NpgsqlParameter("idmovie", NpgsqlDbType.Integer)     { Value = idMovie },
                                                           new NpgsqlParameter("iddirector", NpgsqlDbType.Integer)  { Value = directorID }
                                                          };

                    insertMovieDirectorCmd.Parameters.AddRange(directorParamList);

                    int rows_affected = insertMovieDirectorCmd.ExecuteNonQuery();

                    if (rows_affected != 1)
                    {
                        throw new Exception("Insert into movie_director table failed");
                    }
                }

            }

            foreach (Genre genre in genres)
            {
                NpgsqlCommand insertMovieGenreCmd = new NpgsqlCommand(@"INSERT INTO movie_genre(idmovie, idgenre) VALUES (:idmovie, :idgenre)", connection);

                string genreName = genre.GenreType.Trim();

                if (IDgenreMapping.ContainsKey(genreName))
                {
                    int genreId = IDgenreMapping[genreName];

                    NpgsqlParameter[] genreParamList = {
                                                           new NpgsqlParameter("idmovie", NpgsqlDbType.Integer) { Value = idMovie },
                                                           new NpgsqlParameter("idgenre", NpgsqlDbType.Integer) { Value = genreId }
                                                       };

                    insertMovieGenreCmd.Parameters.AddRange(genreParamList);

                    int rows_affected = insertMovieGenreCmd.ExecuteNonQuery();

                    if (rows_affected != 1)
                    {
                        throw new Exception("Insert into movie_genre table failed");
                    }

                }
                //else
                //{
                //    throw new ArgumentException("Genre " + genreName + " is not in genres table.");
                //}

            }

            foreach (Review review in reviews)
            {
                NpgsqlCommand insertReviewCmd = new NpgsqlCommand(@"INSERT INTO review(idmovie, reviewtext) VALUES (:idmovie, :reviewtext)", connection);

                NpgsqlParameter[] reviewParamList = {
                                                           new NpgsqlParameter("idmovie", NpgsqlDbType.Integer) { Value = idMovie },
                                                           new NpgsqlParameter("reviewtext", NpgsqlDbType.Text) { Value = review.ReviewText }
                                                    };

                insertReviewCmd.Parameters.AddRange(reviewParamList);

                int rows_affected = insertReviewCmd.ExecuteNonQuery();

                if (rows_affected != 1)
                {
                    throw new Exception("Insert into review table failed");
                }
            }

        }

        private int persistDirectorToDB(string fullname)
        {
            NpgsqlCommand insertDirectorCmd = new NpgsqlCommand(@"INSERT INTO director(fullname) VALUES(:fullname) RETURNING idDirector", connection);

            insertDirectorCmd.Parameters.Add(new NpgsqlParameter("fullname", NpgsqlDbType.Text) { Value = fullname });

            object directorID = insertDirectorCmd.ExecuteScalar();

            if (directorID == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(directorID);
            }
        }

        private int getDirectorID(string fullname)
        {
            NpgsqlCommand selectDirectorCmd = new NpgsqlCommand(@"SELECT iddirector FROM director WHERE fullname=:fullname", connection);

            selectDirectorCmd.Parameters.Add(new NpgsqlParameter("fullname", NpgsqlDbType.Text) { Value = fullname });

            object directorID = selectDirectorCmd.ExecuteScalar();

            if (directorID == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(directorID);
            }
        }
    }
}
